let express = require('express')
let router = express.Router()

let mysqlHelper = require('../utils/mysqlHelper.js')
const utils = require('../utils/utils.js')

// 查询
router.get('/', async function (req, res, next) {
    let result = {}
    try {
        let sqlWhere = " where 1=1"
        sqlWhere += ((req.query.username != '' && typeof (req.query.username) != "undefined") ? " and a.username like '%" + req.query.username + "%'" : "")
        sqlWhere += ((req.query.name != '' && typeof (req.query.name) != "undefined") ? " and a.name like '%" + req.query.userid + "%'" : "")
        let sqlLimit = ((req.query._start != '' && typeof (req.query._start) != "undefined") ? " limit " + req.query._start : "")
        sqlLimit += ((req.query._limit != '' && typeof (req.query._limit) != "undefined") ? " " + req.query._limit : "")
        result.total = (await mysqlHelper.query("select count(1) as ct from sys_user a" + sqlWhere))[0].ct
        let sqlStr = "select * from sys_user a "
        sqlStr += sqlWhere + " order by convert(a.name using gbk), username  " + sqlLimit
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
    } catch (err) {
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 查询所有用户
router.get('/all', async function (req, res, next) {
    let result = {}
    try {
        let sqlStr = "select a.userID as value, a.name as label,c.alias from sys_user a left join sys_user_role b on(a.userId=b.userId) left join sys_role c on (b.roleId=c.roleId)"
        result.code = 200
        result.data = await mysqlHelper.query(sqlStr)

    } catch (err) {
        result.code = 400
        result.err = err.message
    }
    res.json(result)
})

// 查询所有用户 树结构
router.get('/tree', async function (req, res, next) {
    let result = {}
    try {


        let orgdata = await mysqlHelper.query("select * from sys_organization")
        let userdata = await mysqlHelper.query("select * from sys_user")
        let rootrows = orgdata.filter(row => row.parentId == '0')

        let resultdata = []
        rootrows.forEach(element => {
            let jsondata = {
                label: element.orgName,
                value: '1_' + element.orgId,
                children: []
            }

            //筛选台站
            let secondlevel = orgdata.filter(row => row.parentId == element.orgId)
            secondlevel.forEach(seelement => {
                let se_jsondata = {
                    label: seelement.orgName,
                    value: '2_' + seelement.orgId,
                    children: []
                }

                let users = userdata.filter(row => row.orgId == seelement.orgId)
                if (users.length > 0) {
                    users.forEach(user => {
                        se_jsondata.children.push({
                            label: user.name,
                            value: user.userId,
                            id: user.userId,
                        })
                    });
                }
                else
                    se_jsondata.disabled = true

                jsondata.children.push(se_jsondata)
            });


            //筛选直属用户
            let secondlevel_u = userdata.filter(row => row.orgId == element.orgId)
            secondlevel_u.forEach(user => {
                jsondata.children.push({
                    label: user.name,
                    value: user.userId,
                    id: user.userId,
                })
            })

            if (secondlevel.length == 0 && secondlevel_u.length == 0)
                jsondata.disabled = true

            resultdata.push(jsondata)
        });

        result.code = 200
        result.data = resultdata

    } catch (err) {
        result.code = 400
        result.err = err.message
    }
    res.json(result)
})


// 查询 maoyuan  keyword检索
router.get('/list', async function (req, res, next) {
    let result = {}
    try {
        let page = utils.getQueryValue(req.query.page)
        let pageSize = utils.getQueryValue(req.query.pageSize)
        let sqlLimit = " limit " + (page - 1) * pageSize + ", " + pageSize
        sqlLimit = ((sqlLimit.indexOf('NaN') > -1 || sqlLimit.indexOf("undefined") > -1 || pageSize == '' || page == '') ? "" : sqlLimit)
        let keyword = utils.getQueryValue(req.query.keyword)
        let groupId = utils.getQueryValue(req.query.groupId)
        let sqlWhere = " WHERE 1=1"
        sqlWhere += (keyword != '' ? " and (name like '%" + keyword + "%' or userName like '%" + keyword + "%') " : "")
        sqlWhere += (groupId != '' ? " and orgId = " + groupId : "")
        total = (await mysqlHelper.query("select count(1) as ct from sys_user u " + sqlWhere))[0].ct
        let prop = utils.getQueryValue(req.query.prop)
        let order = utils.getQueryValue(req.query.order)
        if (order == 'ascending')
            order = "ASC"
        else if (order == 'descending')
            order = 'DESC'
        let sqlOrder = " ORDER by " + prop + " " + order
        sqlOrder = ((prop.indexOf('NaN') > -1 || order.indexOf("undefined") > -1 || prop == '' || order == '') ? "" : sqlOrder)

        let sqlStr = "select * from (    \
                        SELECT u.userID as id,u.userName,avatar,mail,name,createtime as date,u.status,u.orgId,u.orgId as dept,o.orgName,getParentList(o.orgId) as fullorgName,roleIds as 'group',roleNames as 'groupName',sex,phone   \
                        from sys_user u  \
                        left join (SELECT userId, GROUP_CONCAT(roleId) as roleIds, GROUP_CONCAT(roleName) roleNames   \
                                        FROM (     \
                                        select a.id,a.userId,b.userName,a.roleId,c.roleName from sys_user_role a   \
                                        left join sys_user b on a.userId=b.userId   \
                                        left join sys_role c on a.roleId=c.roleId ) aa   \
                                        GROUP BY userId   \
                                        ) g   \
                        on u.userId=g.userId          \
                        left join sys_organization o on u.orgId=o.orgId  \
                      ) raw "
        sqlStr += sqlWhere + sqlOrder + sqlLimit
        rows = await mysqlHelper.query(sqlStr)
        rows.forEach((user, index, arr) => {
            user.group = (user.group ? user.group.split(',') : [])
        })
        result.code = 200
        result.data = {
            'total': total,
            'page': page,
            'pageSize': pageSize,
            'summary': {
                'id': page * pageSize,
                'name': '999'
            },
            rows: rows
        }
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 新增修改 maoyuan
router.post('/', async function (req, res, next) {
    let result = {}
    try {
        let sqlStr = ""
        let id = utils.getQueryValue(req.body.id)
        let groups = utils.getQueryValue(req.body.group)
        if (id == '') { //新增
            if ((await mysqlHelper.query("select count(1) as ct from sys_user where username='" + req.body.userName + "'"))[0].ct > 0) {
                throw new Error('该用户名已被使用。')
            } else {
                sqlStr = "insert into sys_user(userName,password,name,sex,phone,orgId,status) values ('" + req.body.userName + "','" + req.body.password + "','" + req.body.name + "'," + (req.body.sex === '' ? 'null' : req.body.sex) + ",'" + req.body.phone + "'," + req.body.dept[req.body.dept.length - 1] + "," + req.body.status + ")"
                await mysqlHelper.query(sqlStr)
                groups.forEach((group, index, arr) => {
                    sqlStr = "insert into sys_user_role(userId,roleId) select userId," + group + " from sys_user where userName='" + req.body.userName + "';"
                    result.data = mysqlHelper.query(sqlStr)
                })
                // result.data = await mysqlHelper.query(sqlStr)
            }
        } else { //修改
            let deptStr = Array.isArray(req.body.dept) ? req.body.dept[req.body.dept.length - 1] : req.body.dept
            //let sexStr = utils.getQueryValue(req.body.sex) == '' ? "" : ",sex=" + req.body.sex
            sqlStr = "UPDATE sys_user SET name='" + req.body.name + "',sex=" + (req.body.sex === '' ? 'null' : req.body.sex) + ",phone='" + req.body.phone + "',orgId=" + deptStr + ",status=" + req.body.status + " WHERE userId=" + id
            await mysqlHelper.query(sqlStr)
            sqlStr = "DELETE FROM sys_user_role WHERE userId=" + id + ";"
            await mysqlHelper.query(sqlStr)
            groups.forEach((group, index, arr) => {
                sqlStr = "insert into sys_user_role(userId,roleId) select userId," + group + " from sys_user where userName='" + req.body.userName + "';"
                mysqlHelper.query(sqlStr)
            })
        }
        result.code = 200
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 修改
router.put('/', async function (req, res, next) {
    let result = {}
    try {
        let sqlStr = "update sys_user set password='" + req.body.password + "',name='" + req.body.name + "',sex='" + req.body.sex + "',phone='" + req.body.phone + "',department='" + req.body.department + "' where username='" + req.body.username + "'"
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
        result.code = 200
    } catch (err) {
        result.code = 400
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 配置角色
router.put('/roles', async function (req, res, next) {
    let result = {}
    try {
        let username = req.body.username
        let roles = req.body.roles
        let hasAnyRoles = false
        let sqlStr = "insert into sys_user_role(username,roleId) values "
        for (let i = 0; i < roles.length; i++) {
            if (roles[i] == null || roles[i] == '') {
                continue
            }
            sqlStr += "('" + username + "'," + roles[i] + ")"
            if (i != roles.length - 1) {
                sqlStr += ","
            }
            hasAnyRoles = true
        }
        await mysqlHelper.query("delete from sys_user_role where username='" + username + "'")
        if (hasAnyRoles) {
            result.data = await mysqlHelper.query(sqlStr)
        }
        result.code = 200
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 修改密码
router.put('/changepassword', async function (req, res, next) {
    let result = {}
    try {
        if ((await mysqlHelper.query("select count(1) as ct from sys_user where userName='" + req.body.userName + "' and password='" + req.body.originalPassword + "'"))[0].ct == 0) {
            throw new Error('原密码不正确。')
        }
        let sqlStr = "update sys_user set password='" + req.body.password + "' where userName='" + req.body.userName + "'"
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
        result.code = 200
    } catch (err) {
        result.code = 400
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 重置密码  -maoyuan
router.put('/resetpassword', async function (req, res, next) {
    let result = {}
    try {
        // if ((await mysqlHelper.query("select count(1) as ct from sys_user where userId in (" + req.body.userIds + ")"))[0].ct == 0) {
        //     throw new Error(' 帐号不存在。')
        // }
        let sqlStr = "update sys_user set password='123456' where userId in (" + req.body.userIds.join() + ")"
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
        result.code = 200
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 删除  maoyuan
router.delete('/', async function (req, res, next) {
    let result = {}
    try {
        await mysqlHelper.query("delete from sys_user_role where userId in (" + req.body.userIds.join() + ")")
        result.code = 200
        result.data = await mysqlHelper.query("delete from sys_user where userId in (" + req.body.userIds.join() + ")")
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

module.exports = router
